﻿/*
 * 作者：杨云，黄港
 * 功能描述：用户管理
 * 时间：2018-01-16
 * 修改人：
 * 修改时间：
 *
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Entity;
using Common;
using DataAccess;


namespace DataBusiness
{
    public class UserBLL
    {

        /// <summary> 根据条件查询用户信息 </summary>
        /// <param name="sUserName">姓名</param>
        /// <param name="sUserType">用户类型</param>
        /// <returns>返回用户集合信息</returns>
        public static DataSet GetUsers(string sUserName,string sUserType)
        {
            string sSql = "select * "
                       +" from tb_User  "
                       +" where state='"+ ((int)AppPara.Enum_State.启用).ToString() + "'";

            if (!string.IsNullOrEmpty(sUserName))
                sSql += " and UserName like '"+sUserName+"'";
            if (!string.IsNullOrEmpty(sUserType))
                sSql += " and UserType = '" + sUserType + "'";

            return SqlHelper.QuerySql(sSql);

            //DataSet initDt;
            //initDt= SqlHelper.QuerySql(sSql);
            //initDt.Tables[0].Columns[0].ColumnName = "用户ID";
            //initDt.Tables[0].Columns[1].ColumnName = "用户名";
            //initDt.Tables[0].Columns[2].ColumnName = "姓名";
            //initDt.Tables[0].Columns[3].ColumnName = "密码";
            //initDt.Tables[0].Columns[4].ColumnName = "联系方式";
            //initDt.Tables[0].Columns[5].ColumnName = "用户类型";
            //initDt.Tables[0].Columns[6].ColumnName = "单位";
            //initDt.Tables[0].Columns[7].ColumnName = "职务";
            //initDt.Tables[0].Columns[8].ColumnName = "工作职责";
            //initDt.Tables[0].Columns[9].ColumnName = "状态";
            //return initDt;
        }


        /// <summary>添加用户信息 </summary>
        /// <param name="t">用户对象</param>
        /// <returns>返回所影响的数据行数</returns>
        public static int InsertUser(tb_UserModel t)
        {
            //第一种方式 拼接sql语句字符串
            //string sSql = string.Format("Insert into tb_User(UserID,UserAccount,UserName,Password,Tel,UserType,Company,Position,Duty,State) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')", t.UserID, t.UserAccount, t.UserName, t.UserAccount, t.Tel, t.UserType, t.Company, t.Position, t.Duty, t.State);
            //return SqlHelper.ExeSql(sSql);


            //第二种方式
            string sSql = "Insert into tb_User(UserID,UserAccount,UserName,Password,Tel,UserType,Company,Position,Duty,State) values(@UserID,@UserAccount,@UserName,@Password,@Tel,@UserType,@Company,@Position,@Duty,@State)";

            SqlParameter[] Paras = new SqlParameter[10];
            Paras[0] = new SqlParameter("@UserID", t.UserID);
            Paras[1] = new SqlParameter("@UserAccount", t.UserAccount);
            Paras[2] = new SqlParameter("@UserName", t.UserName);
            Paras[3] = new SqlParameter("@Password", AppMethod.Encrypt(t.UserAccount));
            Paras[4] = new SqlParameter("@Tel", t.Tel);
            Paras[5] = new SqlParameter("@UserType", t.UserType);
            Paras[6] = new SqlParameter("@Company", t.Company);
            Paras[7] = new SqlParameter("@Position", t.Position);
            Paras[8] = new SqlParameter("@Duty", t.Duty);
            Paras[9] = new SqlParameter("@State", t.State);
            return SqlHelper.ExeSql(sSql, Paras);
        }



        /// <summary>修改用户信息 </summary>
        /// <param name="t">用户对象</param>
        /// <returns>返回所影响的数据行数</returns>
        public static int UpdateUser(tb_UserModel t)
        {
            //第一种方式
            //string sSql = string.Format("update tb_User UserAccount='{0}',UserName='{1}',Tel='{2}',UserType='{3}',Company='{4}',Position='{5}',Duty='{6}',State='{7}') where UserID='{8}' ", t.UserAccount, t.UserName, t.Tel, t.UserType, t.Company, t.Position, t.Duty, t.State, t.UserID);
            //return SqlHelper.ExeSql(sSql);


            //第二种方式
            string sSql = "update tb_User UserAccount=@UserAccount,UserName=@UserName,Tel=@Tel,UserType=@UserType,Company=@Company,Position=@Position,Duty=@Duty,State=@State) where UserID=@UserID ";

            SqlParameter[] Paras = new SqlParameter[8];
            Paras[0] = new SqlParameter("@UserAccount", t.UserAccount);
            Paras[1] = new SqlParameter("@UserName", t.UserName);
            Paras[2] = new SqlParameter("@Tel", t.Tel);
            Paras[3] = new SqlParameter("@UserType", t.UserType);
            Paras[4] = new SqlParameter("@Company", t.Company);
            Paras[5] = new SqlParameter("@Position", t.Position);
            Paras[6] = new SqlParameter("@Duty", t.Duty);
            Paras[7] = new SqlParameter("@State", t.State);
            return SqlHelper.ExeSql(sSql, Paras);
        }


        /// <summary>
        /// 按照UserID来删除用户信息
        /// </summary>
        /// <param name="sUserId">用户Id</param>
        /// <returns>返回所影响的数据行数</returns>
        public static int DeleteUser(string sUserId)
        {
            string sSql = string.Format("delete from tb_User where UserID='{0}')", sUserId);
            return SqlHelper.ExeSql(sSql);
        }

        /// <summary>
        /// 删除整张表
        /// </summary>
        /// <returns></returns>
        public static int DeleteUser()
        {
            string sSql = "delete from tb_User";
            return SqlHelper.ExeSql(sSql);
        }

        /// <summary>
        /// 重置用户密码
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public static int ResetPwd(string UserID, string UserName)
        {
            string sPwd = AppMethod.Encrypt(UserName);
            string sSql = "update tb_User set Password ='" + sPwd + "' where UserID ='" + UserID + "'";
            return SqlHelper.ExeSql(sSql);
        }

        /// <summary> 登录 </summary>
        ///  <param name="sUserType">角色</param>
        /// <param name="sUserName">账号</param>
        /// <param name="sPassword">密码</param>
        /// <returns>1:成功   -1：账号不存在  -2: 账号已经停用  -3:密码不正确</returns>
        public static string UserLogin( string sUserName,string sPassword)
        {
            string sSql = "select UserID,UserName,Password,State from tb_User where UserAccount = '" + sUserName + "'";
            DataSet ds = SqlHelper.QuerySql(sSql);
            // 判断有效性
            if (ds.Tables[0].Rows.Count == 0)
                return "-1";   // 账号不存在
            else if (ds.Tables[0].Rows[0]["State"].ToString() == ((int)AppPara.Enum_State.注销).ToString())
                return "-2";   // 账号已经停用
            else if (ds.Tables[0].Rows[0]["Password"].ToString() != AppMethod.Encrypt(sPassword))
                return "-3";   // 密码不正确
            else
                return "1|"+ ds.Tables[0].Rows[0]["UserID"].ToString()+"|"+ds.Tables[0].Rows[0]["UserName"].ToString();  // 成功
        }

    }
}
